package com.samson.empapp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.samson.empapp.dbo.JobsDbo;
import com.samson.empapp.util.DbUtil;

public class JobsDao implements IJobsDao {
	static Logger logger = Logger.getLogger(JobsDao.class);
	
	
	@Override
	public JobsDbo findById(int id) {
		
			JobsDbo obj = null;
			Connection con = DbUtil.getOracleXEConn();
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			StringBuilder sb = null;
			sb = new StringBuilder();
			sb.append("SELECT JOB_ID as jobId, JOB_TITLE jobTitle, MIN_SALARY as minSalary, MAX_SALARY as maxSalary "); 
			sb.append("from JOBS "); 
			sb.append("where JOB_ID=upper('?') ");
			
			try {
				pstmt = con.prepareStatement(sb.toString());
				logger.info("Executing Query " + sb.toString());
				rs = pstmt.executeQuery();
				while (rs.next()) {
					obj = new JobsDbo();
					obj.setJobId(rs.getString("jobId"));
					obj.setJobTitle(rs.getString("jobTitle"));
					obj.setMaxSalary(rs.getInt("maxSalary"));
					obj.setMinSalary(rs.getInt("minSalary"));
											
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				logger.error("Exception" + e);
			} finally {
				DbUtil.closeDBConnection(pstmt, rs, con, sb);
			}
			

			return obj;
	}
	

	@Override
	public List<JobsDbo> findAll() {
		List<JobsDbo> returnList = new ArrayList<JobsDbo>();
		JobsDbo obj = null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("SELECT JOB_ID as jobId, JOB_TITLE jobTitle, MIN_SALARY as minSalary, MAX_SALARY as maxSalary "); 
			sb.append("from JOBS ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				obj = new JobsDbo();
				obj.setJobId(rs.getString("jobId"));
				obj.setJobTitle(rs.getString("jobTitle"));
				obj.setMaxSalary(rs.getInt("maxSalary"));
				obj.setMinSalary(rs.getInt("minSalary"));
				returnList.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("Exception" + e);
		} finally {
			DbUtil.closeDBConnection(pstmt, rs, con, sb);
		}

		return returnList;

	}

	

	@Override
	public void insert(JobsDbo dbo) throws Exception {
		JobsDbo obj=null;
		Connection con = DbUtil.getOracleXEConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuilder sb = null;
		try {
			sb = new StringBuilder();
			sb.append("INSERT INTO \"HR\".\"JOBS\" "); 
			sb.append("(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES "); 
			sb.append("('"+obj.getJobId()+"' /*not nullable*/,'"+obj.getJobTitle()+"' /*not nullable*/,'"+obj.getMinSalary()+"','"+obj.getMaxSalary()+"') ");
			pstmt = con.prepareStatement(sb.toString());
			logger.info("Executing Query " + sb.toString());
			pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("Exception" + e);
			throw e;

		} finally {
			DbUtil.closeDBConnection(pstmt, con, sb);
	} 

	}
	}

